package com.example.android2019.ch3;

import android.content.ContentValues;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

import com.example.android2019.R;

public class Ch3SQLiteActivity extends AppCompatActivity {
    Button btn_insert, btn_select, btn_update, btn_delete, btn_trans;
    MyHelper myHelper;
    SQLiteDatabase db;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_ch3_sqlite);
     /*   inti();*/
        inti();

        myHelper = new MyHelper(this);
        db = myHelper.getWritableDatabase();

        View.OnClickListener listener = new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String sql = "";
                switch (v.getId()) {
                    case R.id.ch3_sqlite_btn_insert:
                      /*  sql="insert into user(name,sex) values(?,?)";
                        db.execSQL(sql,new Object[]{"zhangsan","male"});*/

                        ContentValues cv = new ContentValues();
                        cv.put("name", "lisi");
                        cv.put("sex", "male");
                        db.insert("user", null, cv);
                        Toast.makeText(Ch3SQLiteActivity.this, "添加成功", Toast.LENGTH_SHORT).show();
                        break;
                    case R.id.ch3_sqlite_btn_delete:
                        /*sql="delete from user where id=?";
                        db.execSQL(sql,new Object[]{2});*/

                        db.delete("user", "name=? ", new String[]{"lisi"});

                        Toast.makeText(Ch3SQLiteActivity.this, "删除成功", Toast.LENGTH_SHORT).show();
                        break;
                    case R.id.ch3_sqlite_btn_update:
                        /*sql="update user set name=? where id=?";
                        db.execSQL(sql,new Object[]{"Lily",4});*/
                        ContentValues cv1 = new ContentValues();
                        cv1.put("name", "Lucy");
                        cv1.put("sex", "female");
                        db.update("user", cv1, "name=?", new String[]{"lisi"});

                        Toast.makeText(Ch3SQLiteActivity.this, "更新成功", Toast.LENGTH_SHORT).show();
                        break;
                    case R.id.ch3_sqlite_btn_select:
                       /* sql="select * from user";
                        Cursor cursor=db.rawQuery(sql,null);
                        while (cursor.moveToNext()){
                            String name=cursor.getString(cursor.getColumnIndex("name"));
                            String sex=cursor.getString(cursor.getColumnIndex("sex"));
                            Toast.makeText(Ch3SQLiteActivity.this, name+":"+sex, Toast.LENGTH_SHORT).show();
                        }*/
                        Cursor cursor = db.query("user", new String[]{"name", "sex"}, null, null, null, null, null, null);
                        while (cursor.moveToNext()) {
                            String name = cursor.getString(cursor.getColumnIndex("name"));
                            String sex = cursor.getString(cursor.getColumnIndex("sex"));
                            Toast.makeText(Ch3SQLiteActivity.this, name + ":" + sex, Toast.LENGTH_SHORT).show();
                        }
                        break;
                    case R.id.ch3_sqlite_btn_trans:
                        /* 事务中包含若干条SQL语句(增删改)，这些语句要么全部执行成功，要么全部不执行
                         * 操作： 1）开启事务
                          *      2）执行若干条SQL语句
                          *         （若执行过程有异常，会自动回滚事务之前的状态）
                          *      3）标记事务成功
                          *      4）关闭事务*/
                        db.beginTransaction();
                        String sql1 = "update user set name=?";
                        String sql2 = "update user set sex=?";
                        try {
                            db.execSQL(sql1, new Object[]{"pan"});
                            db.execSQL(sql2, new Object[]{"femal"});
                            db.setTransactionSuccessful();
                        } catch (Exception e) {
                            Log.v("trans", e.getMessage());
                        } finally {
                            db.endTransaction();
                        }
                        break;
                }
            }
        };
        btn_insert.setOnClickListener(listener);
        btn_select.setOnClickListener(listener);
        btn_update.setOnClickListener(listener);
        btn_delete.setOnClickListener(listener);
        btn_trans.setOnClickListener(listener);

        /*获取传递的数据*/
        Intent intent = this.getIntent();
        String a = intent.getStringExtra("test");
        Toast.makeText(this, a, Toast.LENGTH_SHORT).show();
    }

    private void inti() {
        btn_insert = findViewById(R.id.ch3_sqlite_btn_insert);
        btn_select = findViewById(R.id.ch3_sqlite_btn_select);
        btn_update = findViewById(R.id.ch3_sqlite_btn_update);
        btn_delete = findViewById(R.id.ch3_sqlite_btn_delete);
        btn_trans = findViewById(R.id.ch3_sqlite_btn_trans);
    }
}
